package zmee.pur.src.report;

import kd.bos.algo.DataSet;
import kd.bos.dataentity.entity.DynamicObjectCollection;
import kd.bos.db.DB;
import kd.bos.db.DBRoute;
import kd.bos.entity.report.AbstractReportListDataPlugin;
import kd.bos.entity.report.FilterInfo;
import kd.bos.entity.report.ReportQueryParam;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Author: xw
 * @Date: 2023/01/09/15:20
 * @Description: 招标情况汇总报表查询插件；查询维度：招标单位（采购组织）、年、月
 */
public class BidSummaryRptListDataPlugin extends AbstractReportListDataPlugin {
    @Override
    public DataSet query(ReportQueryParam reportQueryParam, Object o) throws Throwable {
        FilterInfo filter = reportQueryParam.getFilter();
        List<Object> params = new ArrayList<Object>();
        DynamicObjectCollection orgs = filter.getDynamicObjectCollection("zmee_org");
        StringBuilder orgWhere = new StringBuilder();
        if (orgs != null) {
            StringBuilder sb = new StringBuilder("");
            orgs.forEach(org -> {
                sb.append("'").append(org.getLong("id")).append("',");
            });
            sb.deleteCharAt(sb.length() - 1);
            orgWhere.append("AND t1.FORGID IN (").append(sb).append(")");
        }
        orgWhere.append(" GROUP BY FORGID");
        Date time = filter.getDate("zmee_time");
        String ym = formattedDateToyyMM(time);
        String y = formattedDateToyy(time);
        params.add(ym);
        params.add(y);
        params.add(ym);
        String sql = "/*dialect*/SELECT t4.zmee_tenderorg," +
                "t3.zmee_currmonthamount," +
                "t3.zmee_qtytotal," +
                "t3.zmee_openbidqty," +
                "t3.zmee_consultationsqty," +
                "t3.zmee_negotiationqty," +
                "t3.zmee_inquiryqty," +
                "t3.zmee_purchaseqty," +
                "t3.zmee_priceratioqty," +
                "t3.zmee_zfpurchaseqty," +
                "t4.zmee_qtyytotal," +
                "t4.zmee_yopenbidqty," +
                "t4.zmee_yconsultationsqty," +
                "t4.zmee_ynegotiationqty," +
                "t4.zmee_yinquiryqty," +
                "t4.zmee_ypurchaseqty," +
                "t4.zmee_ypriceratioqty," +
                "t4.zmee_yzfpurchaseqty," +
                "t4.zmee_otherqty," +
                "t4.zmee_totalamount FROM (" +
                "SELECT" +
                " t1.FORGID AS zmee_tenderorg," +
                "SUM( t3.FTAXAMOUNT )/ 10000 zmee_currmonthamount," +
                "COUNT( t1.fsourcetypeid IN ('768576290574865408','1577539976378200064','791084569175536640','1577545562578758656'," +
                "'791084800692727808','1577545268532882432','1577545449768758272') OR NULL ) AS zmee_qtytotal," +
                "COUNT( t1.fsourcetypeid = '768576290574865408' OR NULL ) AS zmee_openbidqty," +
                "COUNT( t1.fsourcetypeid = '1577539976378200064' OR NULL ) AS zmee_consultationsqty," +
                "COUNT( t1.fsourcetypeid = '791084569175536640' OR NULL ) AS zmee_negotiationqty," +
                "COUNT( t1.fsourcetypeid = '1577545562578758656' OR NULL ) AS zmee_inquiryqty," +
                "COUNT( t1.fsourcetypeid = '791084800692727808' OR NULL ) AS zmee_purchaseqty," +
                "COUNT( t1.fsourcetypeid = '1577545268532882432' OR NULL ) AS zmee_priceratioqty," +
                "COUNT( t1.fsourcetypeid = '1577545449768758272' OR NULL ) AS zmee_zfpurchaseqty FROM t_src_project t1 LEFT JOIN " +
                "t_src_project_t t2 ON t1.FID = t2.FID LEFT JOIN ( SELECT FPARENTID, SUM(FTAXAMOUNT) FTAXAMOUNT FROM t_src_decisionsumsup" +
                " GROUP BY FID ) t3 ON t1.FID = t3.fparentid WHERE " +
                "DATE_FORMAT( t2.fauditdate, '%y-%m' ) = ? AND t1.FCURRENTNODE IN ( '778748079732982784', " +
                "'1298550339443858432', '784475812626712576', '844664748246060032', '1291998834741779456' )" +
                " AND t1.fsourcetypeid IN ('768576290574865408','1577539976378200064','791084569175536640','1577545562578758656'," +
                "'791084800692727808','1577545268532882432','1577545449768758272') AND t1.FOPENSTATUS " +
                "IN ( '9', 'A' ) AND t2.FBILLSTATUS = 'C' " + orgWhere + ") t3 RIGHT JOIN (" +
                "SELECT" +
                " t1.FORGID AS zmee_tenderorg," +
                "SUM( t3.FTAXAMOUNT )/ 10000 zmee_totalamount," +
                "COUNT( t1.fsourcetypeid IN ('768576290574865408','1577539976378200064','791084569175536640','1577545562578758656'," +
                "'791084800692727808','1577545268532882432','1577545449768758272') OR NULL ) AS zmee_qtyytotal," +
                "COUNT( t1.fsourcetypeid = '768576290574865408' OR NULL ) AS zmee_yopenbidqty," +
                "COUNT( t1.fsourcetypeid = '1577539976378200064' OR NULL ) AS zmee_yconsultationsqty," +
                "COUNT( t1.fsourcetypeid = '791084569175536640' OR NULL ) AS zmee_ynegotiationqty," +
                "COUNT( t1.fsourcetypeid = '1577545562578758656' OR NULL ) AS zmee_yinquiryqty," +
                "COUNT( t1.fsourcetypeid = '791084800692727808' OR NULL ) AS zmee_ypurchaseqty," +
                "COUNT( t1.fsourcetypeid = '1577545268532882432' OR NULL ) AS zmee_ypriceratioqty," +
                "COUNT( t1.fsourcetypeid = '1577545449768758272' OR NULL ) AS zmee_yzfpurchaseqty," +
                "COUNT( t1.fsourcetypeid NOT IN ('768576290574865408','1577539976378200064','791084569175536640'," +
                "'1577545562578758656','791084800692727808','1577545268532882432','1577545449768758272') OR NULL ) AS zmee_otherqty FROM " +
                "t_src_project t1 LEFT JOIN " +
                "t_src_project_t t2 ON t1.FID = t2.FID LEFT JOIN ( SELECT FPARENTID, SUM(FTAXAMOUNT) FTAXAMOUNT FROM t_src_decisionsumsup" +
                " GROUP BY FID ) t3 ON t1.FID = t3.fparentid WHERE " +
                "DATE_FORMAT( t2.fauditdate, '%y' ) = ? AND DATE_FORMAT( t2.fauditdate, '%y-%m' ) <= ? AND t1.FCURRENTNODE IN ( " +
                "'778748079732982784', '1298550339443858432', '784475812626712576', '844664748246060032', '1291998834741779456' ) AND t1" +
                ".fsourcetypeid IN ('768576290574865408','1577539976378200064','791084569175536640','1577545562578758656'," +
                "'791084800692727808','1577545268532882432','1577545449768758272') AND t1.FOPENSTATUS IN ( '9', 'A' ) AND t2" +
                ".FBILLSTATUS = 'C' " + orgWhere + " ) t4 ON t3.zmee_tenderorg = t4.zmee_tenderorg";
        return DB.queryDataSet(this.getClass().getName(), DBRoute.of("scm"), sql, params.toArray());
    }

    private String formattedDateToyyMM(Date date) {
        SimpleDateFormat sdf = new SimpleDateFormat("yy-MM");
        return sdf.format(date);
    }

    private String formattedDateToyy(Date date) {
        SimpleDateFormat sdf = new SimpleDateFormat("yy");
        return sdf.format(date);
    }
}
